{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\ljt\\Anaconda3\\lib\\site-packages\\IPython\\core\\interactiveshell.py:2802: DtypeWarning: Columns (8) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  if self.run_code(code, result):\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>UID</th>\n",
       "      <th>channel</th>\n",
       "      <th>day</th>\n",
       "      <th>time</th>\n",
       "      <th>trans_amt</th>\n",
       "      <th>amt_src1</th>\n",
       "      <th>merchant</th>\n",
       "      <th>code1</th>\n",
       "      <th>code2</th>\n",
       "      <th>trans_type1</th>\n",
       "      <th>...</th>\n",
       "      <th>bal</th>\n",
       "      <th>amt_src2</th>\n",
       "      <th>acc_id2</th>\n",
       "      <th>acc_id3</th>\n",
       "      <th>geo_code</th>\n",
       "      <th>trans_type2</th>\n",
       "      <th>market_code</th>\n",
       "      <th>market_type</th>\n",
       "      <th>ip1_sub</th>\n",
       "      <th>Tag</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10000</td>\n",
       "      <td>140.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>12:23:56</td>\n",
       "      <td>5536.0</td>\n",
       "      <td>f29829bc82459191</td>\n",
       "      <td>88aa547576f43f85</td>\n",
       "      <td>02220ae6dcef9e6e</td>\n",
       "      <td>e351b7481d292c20</td>\n",
       "      <td>c2f2023d279665b2</td>\n",
       "      <td>...</td>\n",
       "      <td>100.0</td>\n",
       "      <td>9a8ee16bde15e38a</td>\n",
       "      <td>65407f8f309309a6</td>\n",
       "      <td>4c287b179b08eb62</td>\n",
       "      <td>wssp</td>\n",
       "      <td>105.0</td>\n",
       "      <td>217181f12d534065</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1e3ea9498c461cbf</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10000</td>\n",
       "      <td>140.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>12:24:17</td>\n",
       "      <td>5536.0</td>\n",
       "      <td>f29829bc82459191</td>\n",
       "      <td>88aa547576f43f85</td>\n",
       "      <td>02220ae6dcef9e6e</td>\n",
       "      <td>e351b7481d292c20</td>\n",
       "      <td>c2f2023d279665b2</td>\n",
       "      <td>...</td>\n",
       "      <td>100.0</td>\n",
       "      <td>9a8ee16bde15e38a</td>\n",
       "      <td>65407f8f309309a6</td>\n",
       "      <td>4c287b179b08eb62</td>\n",
       "      <td>wssp</td>\n",
       "      <td>105.0</td>\n",
       "      <td>217181f12d534065</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1e3ea9498c461cbf</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10001</td>\n",
       "      <td>102.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>15:04:10</td>\n",
       "      <td>81648.0</td>\n",
       "      <td>155c9e1c32bd0fa2</td>\n",
       "      <td>d8babe2d19fa0c08</td>\n",
       "      <td>02220ae6dcef9e6e</td>\n",
       "      <td>e351b7481d292c20</td>\n",
       "      <td>6d55c54c8b1056fb</td>\n",
       "      <td>...</td>\n",
       "      <td>75853.0</td>\n",
       "      <td>9fefed0a981dcb7a</td>\n",
       "      <td>65407f8f309309a6</td>\n",
       "      <td>4c287b179b08eb62</td>\n",
       "      <td>wssp</td>\n",
       "      <td>102.0</td>\n",
       "      <td>217181f12d534065</td>\n",
       "      <td>1.0</td>\n",
       "      <td>ae6329fa49927bfc</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     UID  channel   day      time  trans_amt          amt_src1  \\\n",
       "0  10000    140.0  26.0  12:23:56     5536.0  f29829bc82459191   \n",
       "1  10000    140.0  26.0  12:24:17     5536.0  f29829bc82459191   \n",
       "2  10001    102.0  13.0  15:04:10    81648.0  155c9e1c32bd0fa2   \n",
       "\n",
       "           merchant             code1             code2       trans_type1 ...  \\\n",
       "0  88aa547576f43f85  02220ae6dcef9e6e  e351b7481d292c20  c2f2023d279665b2 ...   \n",
       "1  88aa547576f43f85  02220ae6dcef9e6e  e351b7481d292c20  c2f2023d279665b2 ...   \n",
       "2  d8babe2d19fa0c08  02220ae6dcef9e6e  e351b7481d292c20  6d55c54c8b1056fb ...   \n",
       "\n",
       "       bal          amt_src2           acc_id2           acc_id3 geo_code  \\\n",
       "0    100.0  9a8ee16bde15e38a  65407f8f309309a6  4c287b179b08eb62     wssp   \n",
       "1    100.0  9a8ee16bde15e38a  65407f8f309309a6  4c287b179b08eb62     wssp   \n",
       "2  75853.0  9fefed0a981dcb7a  65407f8f309309a6  4c287b179b08eb62     wssp   \n",
       "\n",
       "  trans_type2       market_code market_type           ip1_sub Tag  \n",
       "0       105.0  217181f12d534065         1.0  1e3ea9498c461cbf   1  \n",
       "1       105.0  217181f12d534065         1.0  1e3ea9498c461cbf   1  \n",
       "2       102.0  217181f12d534065         1.0  ae6329fa49927bfc   0  \n",
       "\n",
       "[3 rows x 28 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from base_helper import *\n",
    "trsct_rd1 = get_transaction_round1_new()\n",
    "trsct_tr = get_transaction_train_new()\n",
    "tag = get_tag_train_new()\n",
    "sub = get_sub()\n",
    "sub[tag_hd.Tag] = -1\n",
    "# 测试集没有tag \n",
    "trsct_rd1 = sub.merge(trsct_rd1, on='UID', how='left')\n",
    "trsct_tr = tag.merge(trsct_tr, on='UID', how='left')\n",
    "cols = transaction_header + [tag_hd.Tag]\n",
    "trsct_merge = pd.concat([trsct_tr[cols],trsct_rd1[cols]])\n",
    "del trsct_tr,trsct_rd1 \n",
    "trsct_merge = trsct_merge.fillna(method=\"ffill\")\n",
    "# 后向填充，使用下一行的值,不存在的时候就不填充\n",
    "trsct_merge = trsct_merge.fillna(method=\"bfill\") \n",
    "trsct_merge = fill_mean(trsct_merge)\n",
    "trsct_merge.fillna(-1, inplace=True)\n",
    "trsct_merge.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>day</th>\n",
       "      <th>day_uid_cnt</th>\n",
       "      <th>day_trsct_cnt_rate</th>\n",
       "      <th>day_uid_nunique</th>\n",
       "      <th>day_trsct_nunique_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>20427</td>\n",
       "      <td>0.046919</td>\n",
       "      <td>8331</td>\n",
       "      <td>0.116037</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   day  day_uid_cnt  day_trsct_cnt_rate  day_uid_nunique  \\\n",
       "0  1.0        20427            0.046919             8331   \n",
       "\n",
       "   day_trsct_nunique_rate  \n",
       "0                0.116037  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 用户所在的天数之和\n",
    "day_gb = trsct_merge.groupby(tr_hd.day) \n",
    "day_st = day_gb[tag_hd.UID].count().reset_index()\n",
    "day_st.columns = ['day','day_uid_cnt']\n",
    "day_st['day_trsct_cnt_rate'] = day_st['day_uid_cnt']/trsct_merge.shape[0]\n",
    "day_st02 = day_gb[tag_hd.UID].nunique().reset_index()\n",
    "day_st02.columns = ['day','day_uid_nunique']\n",
    "day_st02['day_trsct_nunique_rate'] = day_st['day_uid_cnt']/day_st02['day_uid_nunique'].sum()\n",
    "day_st = day_st.merge(day_st02, on=tr_hd.day, how='left')\n",
    "day_st.head(1)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>channel</th>\n",
       "      <th>channel_uid_cnt</th>\n",
       "      <th>channel_trsct_cnt_rate</th>\n",
       "      <th>channel_uid_nunique</th>\n",
       "      <th>channel_trsct_nunique_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>102.0</td>\n",
       "      <td>129705</td>\n",
       "      <td>0.29792</td>\n",
       "      <td>18509</td>\n",
       "      <td>1.600249</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   channel  channel_uid_cnt  channel_trsct_cnt_rate  channel_uid_nunique  \\\n",
       "0    102.0           129705                 0.29792                18509   \n",
       "\n",
       "   channel_trsct_nunique_rate  \n",
       "0                    1.600249  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 平台类型channel\n",
    "channel_gb = trsct_merge.groupby(tr_hd.channel) \n",
    "channel_st = channel_gb[tag_hd.UID].count().reset_index()\n",
    "channel_st.columns = ['channel','channel_uid_cnt']\n",
    "channel_st['channel_trsct_cnt_rate'] =channel_st['channel_uid_cnt']/trsct_merge.shape[0]\n",
    "channel_st02 = channel_gb[tag_hd.UID].nunique().reset_index()\n",
    "channel_st02.columns = ['channel','channel_uid_nunique']\n",
    "channel_st02['channel_trsct_nunique_rate'] =channel_st['channel_uid_cnt']/channel_st02['channel_uid_nunique'].sum()\n",
    "channel_st = channel_st.merge(channel_st02, on=tr_hd.channel, how='left') \n",
    "channel_st.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>trans_amt</th>\n",
       "      <th>trans_amt_uid_cnt</th>\n",
       "      <th>trans_amt_trsct_cnt_rate</th>\n",
       "      <th>trans_amt_uid_nunique</th>\n",
       "      <th>trans_amt_trsct_nunique_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000002</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>102.0</td>\n",
       "      <td>22194</td>\n",
       "      <td>0.050977</td>\n",
       "      <td>2008</td>\n",
       "      <td>0.090723</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   trans_amt  trans_amt_uid_cnt  trans_amt_trsct_cnt_rate  \\\n",
       "0      100.0                  1                  0.000002   \n",
       "1      102.0              22194                  0.050977   \n",
       "\n",
       "   trans_amt_uid_nunique  trans_amt_trsct_nunique_rate  \n",
       "0                      1                      0.000004  \n",
       "1                   2008                      0.090723  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 不同操作状态下的操作数量，与比值\n",
    "trans_amt_gb = trsct_merge.groupby(tr_hd.trans_amt)\n",
    "trans_amt_st = trans_amt_gb[tag_hd.UID].count().reset_index()\n",
    "trans_amt_st.columns = ['trans_amt','trans_amt_uid_cnt']\n",
    "trans_amt_st['trans_amt_trsct_cnt_rate'] = trans_amt_st['trans_amt_uid_cnt']/trsct_merge.shape[0]\n",
    "trans_amt_st02 = trans_amt_gb[tag_hd.UID].nunique().reset_index()\n",
    "trans_amt_st02.columns = ['trans_amt','trans_amt_uid_nunique']\n",
    "trans_amt_st02['trans_amt_trsct_nunique_rate'] =trans_amt_st['trans_amt_uid_cnt']/trans_amt_st02['trans_amt_uid_nunique'].sum()\n",
    "trans_amt_st = trans_amt_st.merge(trans_amt_st02, on=tr_hd.trans_amt, how='left')\n",
    "trans_amt_st.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>time</th>\n",
       "      <th>UID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00</td>\n",
       "      <td>3157</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01</td>\n",
       "      <td>1583</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>02</td>\n",
       "      <td>1021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>03</td>\n",
       "      <td>748</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>04</td>\n",
       "      <td>967</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>05</td>\n",
       "      <td>1339</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>06</td>\n",
       "      <td>7004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>07</td>\n",
       "      <td>14493</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>08</td>\n",
       "      <td>31409</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>09</td>\n",
       "      <td>39966</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10</td>\n",
       "      <td>38495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>11</td>\n",
       "      <td>33850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>12</td>\n",
       "      <td>28050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>13</td>\n",
       "      <td>23494</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>14</td>\n",
       "      <td>23958</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>15</td>\n",
       "      <td>25864</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>16</td>\n",
       "      <td>28779</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>17</td>\n",
       "      <td>28559</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>18</td>\n",
       "      <td>26777</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>19</td>\n",
       "      <td>24560</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>20</td>\n",
       "      <td>20706</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>21</td>\n",
       "      <td>16170</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>22</td>\n",
       "      <td>9746</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>23</td>\n",
       "      <td>4674</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   time    UID\n",
       "0    00   3157\n",
       "1    01   1583\n",
       "2    02   1021\n",
       "3    03    748\n",
       "4    04    967\n",
       "5    05   1339\n",
       "6    06   7004\n",
       "7    07  14493\n",
       "8    08  31409\n",
       "9    09  39966\n",
       "10   10  38495\n",
       "11   11  33850\n",
       "12   12  28050\n",
       "13   13  23494\n",
       "14   14  23958\n",
       "15   15  25864\n",
       "16   16  28779\n",
       "17   17  28559\n",
       "18   18  26777\n",
       "19   19  24560\n",
       "20   20  20706\n",
       "21   21  16170\n",
       "22   22   9746\n",
       "23   23   4674"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 操作日期 \n",
    "trsct_merge[tr_hd.time] = trsct_merge[tr_hd.time].map(lambda x:x[:2])\n",
    "trsct_merge.groupby(tr_hd.time)[tag_hd.UID].count().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>time</th>\n",
       "      <th>time_uid_cnt</th>\n",
       "      <th>time_trsct_cnt_rate</th>\n",
       "      <th>time_uid_nunique</th>\n",
       "      <th>time_trsct_nunique_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00</td>\n",
       "      <td>3157</td>\n",
       "      <td>0.007251</td>\n",
       "      <td>1309</td>\n",
       "      <td>0.020476</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  time  time_uid_cnt  time_trsct_cnt_rate  time_uid_nunique  \\\n",
       "0   00          3157             0.007251              1309   \n",
       "\n",
       "   time_trsct_nunique_rate  \n",
       "0                 0.020476  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 操作时间点\n",
    "time_gb = trsct_merge.groupby(tr_hd.time)\n",
    "time_st = time_gb[tag_hd.UID].count().reset_index()\n",
    "time_st.columns = ['time','time_uid_cnt']\n",
    "time_st['time_trsct_cnt_rate'] =time_st['time_uid_cnt']/trsct_merge.shape[0]\n",
    "time_st02 = time_gb[tag_hd.UID].nunique().reset_index()\n",
    "time_st02.columns = ['time','time_uid_nunique']\n",
    "time_st02['time_trsct_nunique_rate'] =time_st['time_uid_cnt']/time_st02['time_uid_nunique'].sum()\n",
    "time_st = time_st.merge(time_st02, on=tr_hd.time, how='left')\n",
    "time_st.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>amt_src1</th>\n",
       "      <th>amt_src1_uid_cnt</th>\n",
       "      <th>amt_src1_trsct_cnt_rate</th>\n",
       "      <th>amt_src1_uid_nunique</th>\n",
       "      <th>amt_src1_trsct_nunique_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0b747ef141d49c8c</td>\n",
       "      <td>116</td>\n",
       "      <td>0.000266</td>\n",
       "      <td>58</td>\n",
       "      <td>0.000817</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>155c9e1c32bd0fa2</td>\n",
       "      <td>84714</td>\n",
       "      <td>0.194580</td>\n",
       "      <td>15697</td>\n",
       "      <td>0.596746</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           amt_src1  amt_src1_uid_cnt  amt_src1_trsct_cnt_rate  \\\n",
       "0  0b747ef141d49c8c               116                 0.000266   \n",
       "1  155c9e1c32bd0fa2             84714                 0.194580   \n",
       "\n",
       "   amt_src1_uid_nunique  amt_src1_trsct_nunique_rate  \n",
       "0                    58                     0.000817  \n",
       "1                 15697                     0.596746  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 脱敏后交易金额\n",
    "amt_src1_gb = trsct_merge.groupby(tr_hd.amt_src1)\n",
    "amt_src1_st = amt_src1_gb[tag_hd.UID].count().reset_index()\n",
    "amt_src1_st.columns = ['amt_src1','amt_src1_uid_cnt']\n",
    "amt_src1_st['amt_src1_trsct_cnt_rate'] = amt_src1_st['amt_src1_uid_cnt']/trsct_merge.shape[0]\n",
    "amt_src1_st02 = amt_src1_gb[tag_hd.UID].nunique().reset_index()\n",
    "amt_src1_st02.columns = ['amt_src1','amt_src1_uid_nunique']\n",
    "amt_src1_st02['amt_src1_trsct_nunique_rate'] =amt_src1_st['amt_src1_uid_cnt']/amt_src1_st02['amt_src1_uid_nunique'].sum()\n",
    "amt_src1_st = amt_src1_st.merge(amt_src1_st02, on=tr_hd.amt_src1, how='left')\n",
    "amt_src1_st.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>merchant</th>\n",
       "      <th>merchant_uid_cnt</th>\n",
       "      <th>merchant_trsct_cnt_rate</th>\n",
       "      <th>merchant_uid_nunique</th>\n",
       "      <th>merchant_trsct_nunique_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>000366c02d44d1ec</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000002</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0003b36ff82eca3e</td>\n",
       "      <td>2</td>\n",
       "      <td>0.000005</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000015</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           merchant  merchant_uid_cnt  merchant_trsct_cnt_rate  \\\n",
       "0  000366c02d44d1ec                 1                 0.000002   \n",
       "1  0003b36ff82eca3e                 2                 0.000005   \n",
       "\n",
       "   merchant_uid_nunique  merchant_trsct_nunique_rate  \n",
       "0                     1                     0.000008  \n",
       "1                     1                     0.000015  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#商户标识\n",
    "merchant_gb = trsct_merge.groupby(tr_hd.merchant)\n",
    "merchant_st = merchant_gb[tag_hd.UID].count().reset_index()\n",
    "merchant_st.columns = ['merchant','merchant_uid_cnt']\n",
    "merchant_st['merchant_trsct_cnt_rate'] = merchant_st['merchant_uid_cnt']/trsct_merge.shape[0]\n",
    "merchant_st02 = merchant_gb[tag_hd.UID].nunique().reset_index()\n",
    "merchant_st02.columns = ['merchant','merchant_uid_nunique']\n",
    "merchant_st02['merchant_trsct_nunique_rate'] =merchant_st['merchant_uid_cnt']/merchant_st02['merchant_uid_nunique'].sum()\n",
    "merchant_st = merchant_st.merge(merchant_st02, on=tr_hd.merchant, how='left')\n",
    "merchant_st.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code1</th>\n",
       "      <th>code1_uid_cnt</th>\n",
       "      <th>code1_trsct_cnt_rate</th>\n",
       "      <th>code1_uid_nunique</th>\n",
       "      <th>code1_trsct_nunique_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>001055ae5cf3ab98</td>\n",
       "      <td>28</td>\n",
       "      <td>0.000064</td>\n",
       "      <td>2</td>\n",
       "      <td>0.000371</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00192682a1b2051b</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000002</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000013</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              code1  code1_uid_cnt  code1_trsct_cnt_rate  code1_uid_nunique  \\\n",
       "0  001055ae5cf3ab98             28              0.000064                  2   \n",
       "1  00192682a1b2051b              1              0.000002                  1   \n",
       "\n",
       "   code1_trsct_nunique_rate  \n",
       "0                  0.000371  \n",
       "1                  0.000013  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#商户子门店编码加密\n",
    "code1_gb = trsct_merge.groupby(tr_hd.code1)\n",
    "code1_st = code1_gb[tag_hd.UID].count().reset_index()\n",
    "code1_st.columns = ['code1','code1_uid_cnt']\n",
    "code1_st['code1_trsct_cnt_rate'] = code1_st['code1_uid_cnt']/trsct_merge.shape[0]\n",
    "code1_st02 = code1_gb[tag_hd.UID].nunique().reset_index()\n",
    "code1_st02.columns = ['code1','code1_uid_nunique']\n",
    "code1_st02['code1_trsct_nunique_rate'] =code1_st['code1_uid_cnt']/code1_st02['code1_uid_nunique'].sum()\n",
    "code1_st = code1_st.merge(code1_st02, on=tr_hd.code1, how='left')\n",
    "code1_st.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code2</th>\n",
       "      <th>code2_uid_cnt</th>\n",
       "      <th>code2_trsct_cnt_rate</th>\n",
       "      <th>code2_uid_nunique</th>\n",
       "      <th>code2_trsct_nunique_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00291c748905f303</td>\n",
       "      <td>604</td>\n",
       "      <td>0.001387</td>\n",
       "      <td>64</td>\n",
       "      <td>0.00951</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00d70f6528d856e8</td>\n",
       "      <td>14</td>\n",
       "      <td>0.000032</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00022</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              code2  code2_uid_cnt  code2_trsct_cnt_rate  code2_uid_nunique  \\\n",
       "0  00291c748905f303            604              0.001387                 64   \n",
       "1  00d70f6528d856e8             14              0.000032                  1   \n",
       "\n",
       "   code2_trsct_nunique_rate  \n",
       "0                   0.00951  \n",
       "1                   0.00022  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 商户终端设备标识\n",
    "code2_gb = trsct_merge.groupby(tr_hd.code2)\n",
    "code2_st = code2_gb[tag_hd.UID].count().reset_index()\n",
    "code2_st.columns = ['code2','code2_uid_cnt']\n",
    "code2_st['code2_trsct_cnt_rate'] = code2_st['code2_uid_cnt']/trsct_merge.shape[0]\n",
    "code2_st02 = code2_gb[tag_hd.UID].nunique().reset_index()\n",
    "code2_st02.columns = ['code2','code2_uid_nunique']\n",
    "code2_st02['code2_trsct_nunique_rate'] =code2_st['code2_uid_cnt']/code2_st02['code2_uid_nunique'].sum()\n",
    "code2_st = code2_st.merge(code2_st02, on=tr_hd.code2, how='left')\n",
    "code2_st.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>trans_type1</th>\n",
       "      <th>trans_type1_uid_cnt</th>\n",
       "      <th>trans_type1_trsct_cnt_rate</th>\n",
       "      <th>trans_type1_uid_nunique</th>\n",
       "      <th>trans_type1_trsct_nunique_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>26bcf43a19df14c8</td>\n",
       "      <td>18130</td>\n",
       "      <td>0.041643</td>\n",
       "      <td>5701</td>\n",
       "      <td>0.190383</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3f469aa3836e71cb</td>\n",
       "      <td>18</td>\n",
       "      <td>0.000041</td>\n",
       "      <td>6</td>\n",
       "      <td>0.000189</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        trans_type1  trans_type1_uid_cnt  trans_type1_trsct_cnt_rate  \\\n",
       "0  26bcf43a19df14c8                18130                    0.041643   \n",
       "1  3f469aa3836e71cb                   18                    0.000041   \n",
       "\n",
       "   trans_type1_uid_nunique  trans_type1_trsct_nunique_rate  \n",
       "0                     5701                        0.190383  \n",
       "1                        6                        0.000189  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#交易类型，例如“消费”、“退款”\n",
    "trans_type1_gb = trsct_merge.groupby(tr_hd.trans_type1)\n",
    "trans_type1_st = trans_type1_gb[tag_hd.UID].count().reset_index()\n",
    "trans_type1_st.columns = ['trans_type1','trans_type1_uid_cnt']\n",
    "trans_type1_st['trans_type1_trsct_cnt_rate'] = trans_type1_st['trans_type1_uid_cnt']/trsct_merge.shape[0]\n",
    "trans_type1_st02 = trans_type1_gb[tag_hd.UID].nunique().reset_index()\n",
    "trans_type1_st02.columns = ['trans_type1','trans_type1_uid_nunique']\n",
    "trans_type1_st02['trans_type1_trsct_nunique_rate'] =trans_type1_st['trans_type1_uid_cnt']/trans_type1_st02['trans_type1_uid_nunique'].sum()\n",
    "trans_type1_st = trans_type1_st.merge(trans_type1_st02, on=tr_hd.trans_type1, how='left')\n",
    "trans_type1_st.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>acc_id1</th>\n",
       "      <th>acc_id1_uid_cnt</th>\n",
       "      <th>acc_id1_trsct_cnt_rate</th>\n",
       "      <th>acc_id1_uid_nunique</th>\n",
       "      <th>acc_id1_trsct_nunique_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00017748410eb948</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000002</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0001d1bee525ee6f</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000002</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00001</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            acc_id1  acc_id1_uid_cnt  acc_id1_trsct_cnt_rate  \\\n",
       "0  00017748410eb948                1                0.000002   \n",
       "1  0001d1bee525ee6f                1                0.000002   \n",
       "\n",
       "   acc_id1_uid_nunique  acc_id1_trsct_nunique_rate  \n",
       "0                    1                     0.00001  \n",
       "1                    1                     0.00001  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#acc_id1用户交易账户号编码加密\n",
    "acc_id1_gb = trsct_merge.groupby(tr_hd.acc_id1)\n",
    "acc_id1_st = acc_id1_gb[tag_hd.UID].count().reset_index()\n",
    "acc_id1_st.columns = ['acc_id1','acc_id1_uid_cnt']\n",
    "acc_id1_st['acc_id1_trsct_cnt_rate'] = acc_id1_st['acc_id1_uid_cnt']/trsct_merge.shape[0]\n",
    "acc_id1_st02 = acc_id1_gb[tag_hd.UID].nunique().reset_index()\n",
    "acc_id1_st02.columns = ['acc_id1','acc_id1_uid_nunique']\n",
    "acc_id1_st02['acc_id1_trsct_nunique_rate'] =acc_id1_st['acc_id1_uid_cnt']/acc_id1_st02['acc_id1_uid_nunique'].sum()\n",
    "acc_id1_st = acc_id1_st.merge(acc_id1_st02, on=tr_hd.acc_id1, how='left')\n",
    "acc_id1_st.head(2)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>device_code1</th>\n",
       "      <th>device_code1_uid_cnt</th>\n",
       "      <th>device_code1_trsct_cnt_rate</th>\n",
       "      <th>device_code1_uid_nunique</th>\n",
       "      <th>device_code1_trsct_nunique_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0002150cffad8bee</td>\n",
       "      <td>17</td>\n",
       "      <td>0.000039</td>\n",
       "      <td>2</td>\n",
       "      <td>0.000224</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>000593784c35031d</td>\n",
       "      <td>3</td>\n",
       "      <td>0.000007</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000040</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       device_code1  device_code1_uid_cnt  device_code1_trsct_cnt_rate  \\\n",
       "0  0002150cffad8bee                    17                     0.000039   \n",
       "1  000593784c35031d                     3                     0.000007   \n",
       "\n",
       "   device_code1_uid_nunique  device_code1_trsct_nunique_rate  \n",
       "0                         2                         0.000224  \n",
       "1                         1                         0.000040  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#device_code1\t操作设备唯一标识1\t设备号唯一标识加密，可用于安卓类设备的唯一标识\n",
    "device_code1_gb = trsct_merge.groupby(tr_hd.device_code1)\n",
    "device_code1_st = device_code1_gb[tag_hd.UID].count().reset_index()\n",
    "device_code1_st.columns = ['device_code1','device_code1_uid_cnt']\n",
    "device_code1_st['device_code1_trsct_cnt_rate'] = device_code1_st['device_code1_uid_cnt']/trsct_merge.shape[0]\n",
    "device_code1_st02 = device_code1_gb[tag_hd.UID].nunique().reset_index()\n",
    "device_code1_st02.columns = ['device_code1','device_code1_uid_nunique']\n",
    "device_code1_st02['device_code1_trsct_nunique_rate'] =device_code1_st['device_code1_uid_cnt']/device_code1_st02['device_code1_uid_nunique'].sum()\n",
    "device_code1_st = device_code1_st.merge(device_code1_st02, on=tr_hd.device_code1, how='left')\n",
    "device_code1_st.head(2)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>device_code2</th>\n",
       "      <th>device_code2_uid_cnt</th>\n",
       "      <th>device_code2_trsct_cnt_rate</th>\n",
       "      <th>device_code2_uid_nunique</th>\n",
       "      <th>device_code2_trsct_nunique_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00002ed76eb9d313</td>\n",
       "      <td>6</td>\n",
       "      <td>0.000014</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000078</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00019c2f0e374d03</td>\n",
       "      <td>22</td>\n",
       "      <td>0.000051</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000285</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       device_code2  device_code2_uid_cnt  device_code2_trsct_cnt_rate  \\\n",
       "0  00002ed76eb9d313                     6                     0.000014   \n",
       "1  00019c2f0e374d03                    22                     0.000051   \n",
       "\n",
       "   device_code2_uid_nunique  device_code2_trsct_nunique_rate  \n",
       "0                         1                         0.000078  \n",
       "1                         1                         0.000285  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# device_code2\t操作设备唯一标识2\t设备号唯一标识加密，可用于安卓类设备的唯一标识\n",
    "device_code2_gb = trsct_merge.groupby(tr_hd.device_code2)\n",
    "device_code2_st = device_code2_gb[tag_hd.UID].count().reset_index()\n",
    "device_code2_st.columns = ['device_code2','device_code2_uid_cnt']\n",
    "device_code2_st['device_code2_trsct_cnt_rate'] = device_code2_st['device_code2_uid_cnt']/trsct_merge.shape[0]\n",
    "device_code2_st02 = device_code2_gb[tag_hd.UID].nunique().reset_index()\n",
    "device_code2_st02.columns = ['device_code2','device_code2_uid_nunique']\n",
    "device_code2_st02['device_code2_trsct_nunique_rate'] =device_code2_st['device_code2_uid_cnt']/device_code2_st02['device_code2_uid_nunique'].sum()\n",
    "device_code2_st = device_code2_st.merge(device_code2_st02, on=tr_hd.device_code2, how='left')\n",
    "device_code2_st.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>device_code3</th>\n",
       "      <th>device_code3_uid_cnt</th>\n",
       "      <th>device_code3_trsct_cnt_rate</th>\n",
       "      <th>device_code3_uid_nunique</th>\n",
       "      <th>device_code3_trsct_nunique_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0002730db9a8d576</td>\n",
       "      <td>10</td>\n",
       "      <td>0.000023</td>\n",
       "      <td>5</td>\n",
       "      <td>0.000147</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>000294d4578ef445</td>\n",
       "      <td>51</td>\n",
       "      <td>0.000117</td>\n",
       "      <td>7</td>\n",
       "      <td>0.000749</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       device_code3  device_code3_uid_cnt  device_code3_trsct_cnt_rate  \\\n",
       "0  0002730db9a8d576                    10                     0.000023   \n",
       "1  000294d4578ef445                    51                     0.000117   \n",
       "\n",
       "   device_code3_uid_nunique  device_code3_trsct_nunique_rate  \n",
       "0                         5                         0.000147  \n",
       "1                         7                         0.000749  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#device_code3\t操作设备唯一标识3\t设备号唯一标识加密，可用于苹果类设备的唯一标识\n",
    "device_code3_gb = trsct_merge.groupby(tr_hd.device_code3)\n",
    "device_code3_st = device_code3_gb[tag_hd.UID].count().reset_index()\n",
    "device_code3_st.columns = ['device_code3','device_code3_uid_cnt']\n",
    "device_code3_st['device_code3_trsct_cnt_rate'] = device_code3_st['device_code3_uid_cnt']/trsct_merge.shape[0]\n",
    "device_code3_st02 = device_code3_gb[tag_hd.UID].nunique().reset_index()\n",
    "device_code3_st02.columns = ['device_code3','device_code3_uid_nunique']\n",
    "device_code3_st02['device_code3_trsct_nunique_rate'] =device_code3_st['device_code3_uid_cnt']/device_code3_st02['device_code3_uid_nunique'].sum()\n",
    "device_code3_st = device_code3_st.merge(device_code3_st02, on=tr_hd.device_code3, how='left')\n",
    "device_code3_st.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>device1</th>\n",
       "      <th>device1_uid_cnt</th>\n",
       "      <th>device1_trsct_cnt_rate</th>\n",
       "      <th>device1_uid_nunique</th>\n",
       "      <th>device1_trsct_nunique_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00271009e466f04a</td>\n",
       "      <td>4</td>\n",
       "      <td>0.000009</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000053</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0035e105a783cdfe</td>\n",
       "      <td>4</td>\n",
       "      <td>0.000009</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000053</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            device1  device1_uid_cnt  device1_trsct_cnt_rate  \\\n",
       "0  00271009e466f04a                4                0.000009   \n",
       "1  0035e105a783cdfe                4                0.000009   \n",
       "\n",
       "   device1_uid_nunique  device1_trsct_nunique_rate  \n",
       "0                    1                    0.000053  \n",
       "1                    1                    0.000053  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# device1\t操作设备参数1\t设备名称加密，原字段如“Jack‘s iphone\"\n",
    "device1_gb = trsct_merge.groupby(tr_hd.device1)\n",
    "device1_st = device1_gb[tag_hd.UID].count().reset_index()\n",
    "device1_st.columns = ['device1','device1_uid_cnt']\n",
    "device1_st['device1_trsct_cnt_rate'] = device1_st['device1_uid_cnt']/trsct_merge.shape[0]\n",
    "device1_st02 = device1_gb[tag_hd.UID].nunique().reset_index()\n",
    "device1_st02.columns = ['device1','device1_uid_nunique']\n",
    "device1_st02['device1_trsct_nunique_rate'] =device1_st['device1_uid_cnt']/device1_st02['device1_uid_nunique'].sum()\n",
    "device1_st = device1_st.merge(device1_st02, on=tr_hd.device1, how='left')\n",
    "device1_st.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>device2</th>\n",
       "      <th>device2_uid_cnt</th>\n",
       "      <th>device2_trsct_cnt_rate</th>\n",
       "      <th>device2_uid_nunique</th>\n",
       "      <th>device2_trsct_nunique_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1105</td>\n",
       "      <td>148</td>\n",
       "      <td>0.000340</td>\n",
       "      <td>26</td>\n",
       "      <td>0.001866</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1107</td>\n",
       "      <td>17</td>\n",
       "      <td>0.000039</td>\n",
       "      <td>2</td>\n",
       "      <td>0.000214</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  device2  device2_uid_cnt  device2_trsct_cnt_rate  device2_uid_nunique  \\\n",
       "0    1105              148                0.000340                   26   \n",
       "1    1107               17                0.000039                    2   \n",
       "\n",
       "   device2_trsct_nunique_rate  \n",
       "0                    0.001866  \n",
       "1                    0.000214  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# device2\t操作设备参数2\t设备型号\n",
    "device2_gb = trsct_merge.groupby(tr_hd.device2)\n",
    "device2_st = device2_gb[tag_hd.UID].count().reset_index()\n",
    "device2_st.columns = ['device2','device2_uid_cnt']\n",
    "device2_st['device2_trsct_cnt_rate'] = device2_st['device2_uid_cnt']/trsct_merge.shape[0]\n",
    "device2_st02 = device2_gb[tag_hd.UID].nunique().reset_index()\n",
    "device2_st02.columns = ['device2','device2_uid_nunique']\n",
    "device2_st02['device2_trsct_nunique_rate'] =device2_st['device2_uid_cnt']/device2_st02['device2_uid_nunique'].sum()\n",
    "device2_st = device2_st.merge(device2_st02, on=tr_hd.device2, how='left')\n",
    "device2_st.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>mac1</th>\n",
       "      <th>mac1_uid_cnt</th>\n",
       "      <th>mac1_trsct_cnt_rate</th>\n",
       "      <th>mac1_uid_nunique</th>\n",
       "      <th>mac1_trsct_nunique_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00009922206bab3d</td>\n",
       "      <td>41</td>\n",
       "      <td>0.000094</td>\n",
       "      <td>3</td>\n",
       "      <td>0.000560</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0002613d2a28acb2</td>\n",
       "      <td>2</td>\n",
       "      <td>0.000005</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000027</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               mac1  mac1_uid_cnt  mac1_trsct_cnt_rate  mac1_uid_nunique  \\\n",
       "0  00009922206bab3d            41             0.000094                 3   \n",
       "1  0002613d2a28acb2             2             0.000005                 1   \n",
       "\n",
       "   mac1_trsct_nunique_rate  \n",
       "0                 0.000560  \n",
       "1                 0.000027  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# mac1\tMAC地址\t\"操作设备MAC地址编码加密\n",
    "mac1_gb = trsct_merge.groupby(tr_hd.mac1)\n",
    "mac1_st = mac1_gb[tag_hd.UID].count().reset_index()\n",
    "mac1_st.columns = ['mac1','mac1_uid_cnt']\n",
    "mac1_st['mac1_trsct_cnt_rate'] = mac1_st['mac1_uid_cnt']/trsct_merge.shape[0]\n",
    "mac1_st02 = mac1_gb[tag_hd.UID].nunique().reset_index()\n",
    "mac1_st02.columns = ['mac1','mac1_uid_nunique']\n",
    "mac1_st02['mac1_trsct_nunique_rate'] =mac1_st['mac1_uid_cnt']/mac1_st02['mac1_uid_nunique'].sum()\n",
    "mac1_st = mac1_st.merge(mac1_st02, on=tr_hd.mac1, how='left')\n",
    "mac1_st.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ip1</th>\n",
       "      <th>ip1_uid_cnt</th>\n",
       "      <th>ip1_trsct_cnt_rate</th>\n",
       "      <th>ip1_uid_nunique</th>\n",
       "      <th>ip1_trsct_nunique_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00009740017af4e2</td>\n",
       "      <td>5</td>\n",
       "      <td>0.000011</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000030</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00009c8f8ad72e64</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000002</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000006</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                ip1  ip1_uid_cnt  ip1_trsct_cnt_rate  ip1_uid_nunique  \\\n",
       "0  00009740017af4e2            5            0.000011                1   \n",
       "1  00009c8f8ad72e64            1            0.000002                1   \n",
       "\n",
       "   ip1_trsct_nunique_rate  \n",
       "0                0.000030  \n",
       "1                0.000006  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# ip1\tIP地址\t操作设备IP地址编码加密\n",
    "ip1_gb = trsct_merge.groupby(tr_hd.ip1)\n",
    "ip1_st = ip1_gb[tag_hd.UID].count().reset_index()\n",
    "ip1_st.columns = ['ip1','ip1_uid_cnt']\n",
    "ip1_st['ip1_trsct_cnt_rate'] = ip1_st['ip1_uid_cnt']/trsct_merge.shape[0]\n",
    "ip1_st02 = ip1_gb[tag_hd.UID].nunique().reset_index()\n",
    "ip1_st02.columns = ['ip1','ip1_uid_nunique']\n",
    "ip1_st02['ip1_trsct_nunique_rate'] =ip1_st['ip1_uid_cnt']/ip1_st02['ip1_uid_nunique'].sum()\n",
    "ip1_st = ip1_st.merge(ip1_st02, on=tr_hd.ip1, how='left')\n",
    "ip1_st.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>bal</th>\n",
       "      <th>bal_uid_cnt</th>\n",
       "      <th>bal_trsct_cnt_rate</th>\n",
       "      <th>bal_uid_nunique</th>\n",
       "      <th>bal_trsct_nunique_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100.0</td>\n",
       "      <td>370190</td>\n",
       "      <td>0.850290</td>\n",
       "      <td>61777</td>\n",
       "      <td>3.526089</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>102.0</td>\n",
       "      <td>95</td>\n",
       "      <td>0.000218</td>\n",
       "      <td>46</td>\n",
       "      <td>0.000905</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     bal  bal_uid_cnt  bal_trsct_cnt_rate  bal_uid_nunique  \\\n",
       "0  100.0       370190            0.850290            61777   \n",
       "1  102.0           95            0.000218               46   \n",
       "\n",
       "   bal_trsct_nunique_rate  \n",
       "0                3.526089  \n",
       "1                0.000905  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# bal\t脱敏后账户余额\t保留大小关系\n",
    "bal_gb = trsct_merge.groupby(tr_hd.bal)\n",
    "bal_st = bal_gb[tag_hd.UID].count().reset_index()\n",
    "bal_st.columns = ['bal','bal_uid_cnt']\n",
    "bal_st['bal_trsct_cnt_rate'] = bal_st['bal_uid_cnt']/trsct_merge.shape[0]\n",
    "bal_st02 = bal_gb[tag_hd.UID].nunique().reset_index()\n",
    "bal_st02.columns = ['bal','bal_uid_nunique']\n",
    "bal_st02['bal_trsct_nunique_rate'] =bal_st['bal_uid_cnt']/bal_st02['bal_uid_nunique'].sum()\n",
    "bal_st = bal_st.merge(bal_st02, on=tr_hd.bal, how='left')\n",
    "bal_st.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>acc_id2</th>\n",
       "      <th>acc_id2_uid_cnt</th>\n",
       "      <th>acc_id2_trsct_cnt_rate</th>\n",
       "      <th>acc_id2_uid_nunique</th>\n",
       "      <th>acc_id2_trsct_nunique_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00017748410eb948</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000002</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0001d1bee525ee6f</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000002</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000013</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            acc_id2  acc_id2_uid_cnt  acc_id2_trsct_cnt_rate  \\\n",
       "0  00017748410eb948                1                0.000002   \n",
       "1  0001d1bee525ee6f                1                0.000002   \n",
       "\n",
       "   acc_id2_uid_nunique  acc_id2_trsct_nunique_rate  \n",
       "0                    1                    0.000013  \n",
       "1                    1                    0.000013  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# acc_id2\t账户相关\t转账操作的转出账户号编码加密\n",
    "acc_id2_gb = trsct_merge.groupby(tr_hd.acc_id2)\n",
    "acc_id2_st = acc_id2_gb[tag_hd.UID].count().reset_index()\n",
    "acc_id2_st.columns = ['acc_id2','acc_id2_uid_cnt']\n",
    "acc_id2_st['acc_id2_trsct_cnt_rate'] = acc_id2_st['acc_id2_uid_cnt']/trsct_merge.shape[0]\n",
    "acc_id2_st02 = acc_id2_gb[tag_hd.UID].nunique().reset_index()\n",
    "acc_id2_st02.columns = ['acc_id2','acc_id2_uid_nunique']\n",
    "acc_id2_st02['acc_id2_trsct_nunique_rate'] =acc_id2_st['acc_id2_uid_cnt']/acc_id2_st02['acc_id2_uid_nunique'].sum()\n",
    "acc_id2_st = acc_id2_st.merge(acc_id2_st02, on=tr_hd.acc_id2, how='left')\n",
    "acc_id2_st.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>acc_id3</th>\n",
       "      <th>acc_id3_uid_cnt</th>\n",
       "      <th>acc_id3_trsct_cnt_rate</th>\n",
       "      <th>acc_id3_uid_nunique</th>\n",
       "      <th>acc_id3_trsct_nunique_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0000321fb10493f2</td>\n",
       "      <td>47</td>\n",
       "      <td>0.000108</td>\n",
       "      <td>10</td>\n",
       "      <td>0.000569</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00017748410eb948</td>\n",
       "      <td>17</td>\n",
       "      <td>0.000039</td>\n",
       "      <td>4</td>\n",
       "      <td>0.000206</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            acc_id3  acc_id3_uid_cnt  acc_id3_trsct_cnt_rate  \\\n",
       "0  0000321fb10493f2               47                0.000108   \n",
       "1  00017748410eb948               17                0.000039   \n",
       "\n",
       "   acc_id3_uid_nunique  acc_id3_trsct_nunique_rate  \n",
       "0                   10                    0.000569  \n",
       "1                    4                    0.000206  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# acc_id3\t\t转账操作的转入账户号编码加密\n",
    "acc_id3_gb = trsct_merge.groupby(tr_hd.acc_id3)\n",
    "acc_id3_st = acc_id3_gb[tag_hd.UID].count().reset_index()\n",
    "acc_id3_st.columns = ['acc_id3','acc_id3_uid_cnt']\n",
    "acc_id3_st['acc_id3_trsct_cnt_rate'] = acc_id3_st['acc_id3_uid_cnt']/trsct_merge.shape[0]\n",
    "acc_id3_st02 = acc_id3_gb[tag_hd.UID].nunique().reset_index()\n",
    "acc_id3_st02.columns = ['acc_id3','acc_id3_uid_nunique']\n",
    "acc_id3_st02['acc_id3_trsct_nunique_rate'] =acc_id3_st['acc_id3_uid_cnt']/acc_id3_st02['acc_id3_uid_nunique'].sum()\n",
    "acc_id3_st = acc_id3_st.merge(acc_id3_st02, on=tr_hd.acc_id3, how='left')\n",
    "acc_id3_st.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>geo_code</th>\n",
       "      <th>geocode_uid_cnt</th>\n",
       "      <th>geocode_trsct_cnt_rate</th>\n",
       "      <th>geocode_uid_nunique</th>\n",
       "      <th>geocode_trsct_nunique_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9mue</td>\n",
       "      <td>5</td>\n",
       "      <td>0.000011</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000067</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>d1xe</td>\n",
       "      <td>2</td>\n",
       "      <td>0.000005</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000027</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  geo_code  geocode_uid_cnt  geocode_trsct_cnt_rate  geocode_uid_nunique  \\\n",
       "0     9mue                5                0.000011                    1   \n",
       "1     d1xe                2                0.000005                    1   \n",
       "\n",
       "   geocode_trsct_nunique_rate  \n",
       "0                    0.000067  \n",
       "1                    0.000027  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# geocode\t地理位置\t经纬度GeoHash编码\n",
    "geocode_gb = trsct_merge.groupby(tr_hd.geo_code)\n",
    "geocode_st = geocode_gb[tag_hd.UID].count().reset_index()\n",
    "geocode_st.columns = ['geo_code','geocode_uid_cnt']\n",
    "geocode_st['geocode_trsct_cnt_rate'] = geocode_st['geocode_uid_cnt']/trsct_merge.shape[0]\n",
    "geocode_st02 = geocode_gb[tag_hd.UID].nunique().reset_index()\n",
    "geocode_st02.columns = ['geo_code','geocode_uid_nunique']\n",
    "geocode_st02['geocode_trsct_nunique_rate'] =geocode_st['geocode_uid_cnt']/geocode_st02['geocode_uid_nunique'].sum()\n",
    "geocode_st = geocode_st.merge(geocode_st02, on=tr_hd.geo_code, how='left')\n",
    "geocode_st.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>trans_type2</th>\n",
       "      <th>trans_type2_uid_cnt</th>\n",
       "      <th>trans_type2_trsct_cnt_rate</th>\n",
       "      <th>trans_type2_uid_nunique</th>\n",
       "      <th>trans_type2_trsct_nunique_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>102.0</td>\n",
       "      <td>129677</td>\n",
       "      <td>0.297855</td>\n",
       "      <td>21550</td>\n",
       "      <td>1.607998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>103.0</td>\n",
       "      <td>5939</td>\n",
       "      <td>0.013641</td>\n",
       "      <td>989</td>\n",
       "      <td>0.073644</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   trans_type2  trans_type2_uid_cnt  trans_type2_trsct_cnt_rate  \\\n",
       "0        102.0               129677                    0.297855   \n",
       "1        103.0                 5939                    0.013641   \n",
       "\n",
       "   trans_type2_uid_nunique  trans_type2_trsct_nunique_rate  \n",
       "0                    21550                        1.607998  \n",
       "1                      989                        0.073644  "
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# trans_type2\t交易类型2\t\"交易类型，例如“线上”、“线下”；\n",
    "trans_type2_gb = trsct_merge.groupby(tr_hd.trans_type2)\n",
    "trans_type2_st = trans_type2_gb[tag_hd.UID].count().reset_index()\n",
    "trans_type2_st.columns = ['trans_type2','trans_type2_uid_cnt']\n",
    "trans_type2_st['trans_type2_trsct_cnt_rate'] = trans_type2_st['trans_type2_uid_cnt']/trsct_merge.shape[0]\n",
    "trans_type2_st02 = trans_type2_gb[tag_hd.UID].nunique().reset_index()\n",
    "trans_type2_st02.columns = ['trans_type2','trans_type2_uid_nunique']\n",
    "trans_type2_st02['trans_type2_trsct_nunique_rate'] =trans_type2_st['trans_type2_uid_cnt']/trans_type2_st02['trans_type2_uid_nunique'].sum()\n",
    "trans_type2_st = trans_type2_st.merge(trans_type2_st02, on=tr_hd.trans_type2, how='left')\n",
    "trans_type2_st.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>market_code</th>\n",
       "      <th>market_code_uid_cnt</th>\n",
       "      <th>market_code_trsct_cnt_rate</th>\n",
       "      <th>market_code_uid_nunique</th>\n",
       "      <th>market_code_trsct_nunique_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0083f7e37b71148a</td>\n",
       "      <td>134</td>\n",
       "      <td>0.000308</td>\n",
       "      <td>16</td>\n",
       "      <td>0.001677</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00925e5ca786312d</td>\n",
       "      <td>37</td>\n",
       "      <td>0.000085</td>\n",
       "      <td>5</td>\n",
       "      <td>0.000463</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        market_code  market_code_uid_cnt  market_code_trsct_cnt_rate  \\\n",
       "0  0083f7e37b71148a                  134                    0.000308   \n",
       "1  00925e5ca786312d                   37                    0.000085   \n",
       "\n",
       "   market_code_uid_nunique  market_code_trsct_nunique_rate  \n",
       "0                       16                        0.001677  \n",
       "1                        5                        0.000463  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# market_code\t营销活动号编码\t营销活动号编码加密\n",
    "market_code_gb = trsct_merge.groupby(tr_hd.market_code)\n",
    "market_code_st = market_code_gb[tag_hd.UID].count().reset_index()\n",
    "market_code_st.columns = ['market_code','market_code_uid_cnt']\n",
    "market_code_st['market_code_trsct_cnt_rate'] = market_code_st['market_code_uid_cnt']/trsct_merge.shape[0]\n",
    "market_code_st02 = market_code_gb[tag_hd.UID].nunique().reset_index()\n",
    "market_code_st02.columns = ['market_code','market_code_uid_nunique']\n",
    "market_code_st02['market_code_trsct_nunique_rate'] =market_code_st['market_code_uid_cnt']/market_code_st02['market_code_uid_nunique'].sum()\n",
    "market_code_st = market_code_st.merge(market_code_st02, on=tr_hd.market_code, how='left')\n",
    "market_code_st.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>market_type</th>\n",
       "      <th>market_type_uid_cnt</th>\n",
       "      <th>market_type_trsct_cnt_rate</th>\n",
       "      <th>market_type_uid_nunique</th>\n",
       "      <th>market_type_trsct_nunique_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>182362</td>\n",
       "      <td>0.418868</td>\n",
       "      <td>20677</td>\n",
       "      <td>2.754172</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.0</td>\n",
       "      <td>253007</td>\n",
       "      <td>0.581132</td>\n",
       "      <td>45536</td>\n",
       "      <td>3.821108</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   market_type  market_type_uid_cnt  market_type_trsct_cnt_rate  \\\n",
       "0          1.0               182362                    0.418868   \n",
       "1          2.0               253007                    0.581132   \n",
       "\n",
       "   market_type_uid_nunique  market_type_trsct_nunique_rate  \n",
       "0                    20677                        2.754172  \n",
       "1                    45536                        3.821108  "
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# market_type\t营销活动标识\t营销活动类型\n",
    "market_type_gb = trsct_merge.groupby(tr_hd.market_type)\n",
    "market_type_st = market_type_gb[tag_hd.UID].count().reset_index()\n",
    "market_type_st.columns = ['market_type','market_type_uid_cnt']\n",
    "market_type_st['market_type_trsct_cnt_rate'] = market_type_st['market_type_uid_cnt']/trsct_merge.shape[0]\n",
    "market_type_st02 = market_type_gb[tag_hd.UID].nunique().reset_index()\n",
    "market_type_st02.columns = ['market_type','market_type_uid_nunique']\n",
    "market_type_st02['market_type_trsct_nunique_rate'] =market_type_st['market_type_uid_cnt']/market_type_st02['market_type_uid_nunique'].sum()\n",
    "market_type_st = market_type_st.merge(market_type_st02, on=tr_hd.market_type, how='left')\n",
    "market_type_st.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>amt_src2</th>\n",
       "      <th>amt_src2_uid_cnt</th>\n",
       "      <th>amt_src2_trsct_cnt_rate</th>\n",
       "      <th>amt_src2_uid_nunique</th>\n",
       "      <th>amt_src2_trsct_nunique_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00aba2124ca7287b</td>\n",
       "      <td>2962</td>\n",
       "      <td>0.006803</td>\n",
       "      <td>1105</td>\n",
       "      <td>0.028288</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01d2df370ace8ce4</td>\n",
       "      <td>279</td>\n",
       "      <td>0.000641</td>\n",
       "      <td>96</td>\n",
       "      <td>0.002665</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           amt_src2  amt_src2_uid_cnt  amt_src2_trsct_cnt_rate  \\\n",
       "0  00aba2124ca7287b              2962                 0.006803   \n",
       "1  01d2df370ace8ce4               279                 0.000641   \n",
       "\n",
       "   amt_src2_uid_nunique  amt_src2_trsct_nunique_rate  \n",
       "0                  1105                     0.028288  \n",
       "1                    96                     0.002665  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# amt_src2\t资金类型\t交易资金源类型，与1类型相似，2对银行卡做了细分\n",
    "amt_src2_gb = trsct_merge.groupby(tr_hd.amt_src2)\n",
    "amt_src2_st = amt_src2_gb[tag_hd.UID].count().reset_index()\n",
    "amt_src2_st.columns = ['amt_src2','amt_src2_uid_cnt']\n",
    "amt_src2_st['amt_src2_trsct_cnt_rate'] = amt_src2_st['amt_src2_uid_cnt']/trsct_merge.shape[0]\n",
    "amt_src2_st02 = amt_src2_gb[tag_hd.UID].nunique().reset_index()\n",
    "amt_src2_st02.columns = ['amt_src2','amt_src2_uid_nunique']\n",
    "amt_src2_st02['amt_src2_trsct_nunique_rate'] =amt_src2_st['amt_src2_uid_cnt']/amt_src2_st02['amt_src2_uid_nunique'].sum()\n",
    "amt_src2_st = amt_src2_st.merge(amt_src2_st02, on=tr_hd.amt_src2, how='left')\n",
    "amt_src2_st.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ip1_sub</th>\n",
       "      <th>ip1_sub_uid_cnt</th>\n",
       "      <th>ip1_sub_trsct_cnt_rate</th>\n",
       "      <th>ip1_sub_uid_nunique</th>\n",
       "      <th>ip1_sub_trsct_nunique_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>000154a95c2922b8</td>\n",
       "      <td>2</td>\n",
       "      <td>0.000005</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00030f573c9b9140</td>\n",
       "      <td>148</td>\n",
       "      <td>0.000340</td>\n",
       "      <td>87</td>\n",
       "      <td>0.000946</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            ip1_sub  ip1_sub_uid_cnt  ip1_sub_trsct_cnt_rate  \\\n",
       "0  000154a95c2922b8                2                0.000005   \n",
       "1  00030f573c9b9140              148                0.000340   \n",
       "\n",
       "   ip1_sub_uid_nunique  ip1_sub_trsct_nunique_rate  \n",
       "0                    1                    0.000013  \n",
       "1                   87                    0.000946  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# ip1_sub\tIP地址\t前三位操作设备IP地址编码加密(ip1前三位IP地址）\n",
    "ip1_sub_gb = trsct_merge.groupby(tr_hd.ip1_sub)\n",
    "ip1_sub_st = ip1_sub_gb[tag_hd.UID].count().reset_index()\n",
    "ip1_sub_st.columns = ['ip1_sub','ip1_sub_uid_cnt']\n",
    "ip1_sub_st['ip1_sub_trsct_cnt_rate'] = ip1_sub_st['ip1_sub_uid_cnt']/trsct_merge.shape[0]\n",
    "ip1_sub_st02 = ip1_sub_gb[tag_hd.UID].nunique().reset_index()\n",
    "ip1_sub_st02.columns = ['ip1_sub','ip1_sub_uid_nunique']\n",
    "ip1_sub_st02['ip1_sub_trsct_nunique_rate'] =ip1_sub_st['ip1_sub_uid_cnt']/ip1_sub_st02['ip1_sub_uid_nunique'].sum()\n",
    "ip1_sub_st = ip1_sub_st.merge(ip1_sub_st02, on=tr_hd.ip1_sub, how='left')\n",
    "ip1_sub_st.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>UID</th>\n",
       "      <th>channel</th>\n",
       "      <th>day</th>\n",
       "      <th>time</th>\n",
       "      <th>trans_amt</th>\n",
       "      <th>amt_src1</th>\n",
       "      <th>merchant</th>\n",
       "      <th>code1</th>\n",
       "      <th>code2</th>\n",
       "      <th>trans_type1</th>\n",
       "      <th>...</th>\n",
       "      <th>market_code_uid_nunique</th>\n",
       "      <th>market_code_trsct_nunique_rate</th>\n",
       "      <th>market_type_uid_cnt</th>\n",
       "      <th>market_type_trsct_cnt_rate</th>\n",
       "      <th>market_type_uid_nunique</th>\n",
       "      <th>market_type_trsct_nunique_rate</th>\n",
       "      <th>ip1_sub_uid_cnt</th>\n",
       "      <th>ip1_sub_trsct_cnt_rate</th>\n",
       "      <th>ip1_sub_uid_nunique</th>\n",
       "      <th>ip1_sub_trsct_nunique_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10000</td>\n",
       "      <td>140.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>12</td>\n",
       "      <td>5536.0</td>\n",
       "      <td>f29829bc82459191</td>\n",
       "      <td>88aa547576f43f85</td>\n",
       "      <td>02220ae6dcef9e6e</td>\n",
       "      <td>e351b7481d292c20</td>\n",
       "      <td>c2f2023d279665b2</td>\n",
       "      <td>...</td>\n",
       "      <td>17</td>\n",
       "      <td>0.000751</td>\n",
       "      <td>182362</td>\n",
       "      <td>0.418868</td>\n",
       "      <td>20677</td>\n",
       "      <td>2.754172</td>\n",
       "      <td>3</td>\n",
       "      <td>0.000007</td>\n",
       "      <td>2</td>\n",
       "      <td>0.000019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10000</td>\n",
       "      <td>140.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>12</td>\n",
       "      <td>5536.0</td>\n",
       "      <td>f29829bc82459191</td>\n",
       "      <td>88aa547576f43f85</td>\n",
       "      <td>02220ae6dcef9e6e</td>\n",
       "      <td>e351b7481d292c20</td>\n",
       "      <td>c2f2023d279665b2</td>\n",
       "      <td>...</td>\n",
       "      <td>17</td>\n",
       "      <td>0.000751</td>\n",
       "      <td>182362</td>\n",
       "      <td>0.418868</td>\n",
       "      <td>20677</td>\n",
       "      <td>2.754172</td>\n",
       "      <td>3</td>\n",
       "      <td>0.000007</td>\n",
       "      <td>2</td>\n",
       "      <td>0.000019</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 124 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     UID  channel   day time  trans_amt          amt_src1          merchant  \\\n",
       "0  10000    140.0  26.0   12     5536.0  f29829bc82459191  88aa547576f43f85   \n",
       "1  10000    140.0  26.0   12     5536.0  f29829bc82459191  88aa547576f43f85   \n",
       "\n",
       "              code1             code2       trans_type1  \\\n",
       "0  02220ae6dcef9e6e  e351b7481d292c20  c2f2023d279665b2   \n",
       "1  02220ae6dcef9e6e  e351b7481d292c20  c2f2023d279665b2   \n",
       "\n",
       "             ...             market_code_uid_nunique  \\\n",
       "0            ...                                  17   \n",
       "1            ...                                  17   \n",
       "\n",
       "  market_code_trsct_nunique_rate market_type_uid_cnt  \\\n",
       "0                       0.000751              182362   \n",
       "1                       0.000751              182362   \n",
       "\n",
       "  market_type_trsct_cnt_rate market_type_uid_nunique  \\\n",
       "0                   0.418868                   20677   \n",
       "1                   0.418868                   20677   \n",
       "\n",
       "  market_type_trsct_nunique_rate ip1_sub_uid_cnt ip1_sub_trsct_cnt_rate  \\\n",
       "0                       2.754172               3               0.000007   \n",
       "1                       2.754172               3               0.000007   \n",
       "\n",
       "   ip1_sub_uid_nunique ip1_sub_trsct_nunique_rate  \n",
       "0                    2                   0.000019  \n",
       "1                    2                   0.000019  \n",
       "\n",
       "[2 rows x 124 columns]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# trsct_merge = trsct_merge.merge(day_st, on=tr_hd.day, how='left')\n",
    "# trsct_merge = trsct_merge.merge(channel_st, on=tr_hd.channel, how='left')\n",
    "# trsct_merge = trsct_merge.merge(trans_amt_st, on=tr_hd.trans_amt, how='left')\n",
    "# trsct_merge = trsct_merge.merge(time_st, on=tr_hd.time, how='left')\n",
    "# trsct_merge = trsct_merge.merge(amt_src1_st, on=tr_hd.amt_src1, how='left')\n",
    "# trsct_merge = trsct_merge.merge(merchant_st, on=tr_hd.merchant, how='left')\n",
    "# trsct_merge = trsct_merge.merge(code1_st, on=tr_hd.code1, how='left')\n",
    "# trsct_merge = trsct_merge.merge(code2_st, on=tr_hd.code2, how='left')\n",
    "# trsct_merge = trsct_merge.merge(trans_type1_st, on=tr_hd.trans_type1, how='left')\n",
    "# trsct_merge = trsct_merge.merge(acc_id1_st, on=tr_hd.acc_id1, how='left')\n",
    "# trsct_merge = trsct_merge.merge(mac1_st, on=tr_hd.mac1, how='left')\n",
    "# trsct_merge = trsct_merge.merge(device2_st, on=tr_hd.device2, how='left')\n",
    "# trsct_merge = trsct_merge.merge(device1_st, on=tr_hd.device1, how='left')\n",
    "# trsct_merge = trsct_merge.merge(device_code3_st, on=tr_hd.device_code3, how='left')\n",
    "# trsct_merge = trsct_merge.merge(bal_st, on=tr_hd.bal, how='left')\n",
    "# trsct_merge = trsct_merge.merge(ip1_st, on=tr_hd.ip1, how='left')\n",
    "# trsct_merge = trsct_merge.merge(amt_src2_st, on=tr_hd.amt_src2, how='left')\n",
    "# trsct_merge = trsct_merge.merge(acc_id2_st, on=tr_hd.acc_id2, how='left')\n",
    "# trsct_merge = trsct_merge.merge(acc_id3_st, on=tr_hd.acc_id3, how='left')\n",
    "# trsct_merge = trsct_merge.merge(geocode_st, on=tr_hd.geo_code, how='left')\n",
    "# trsct_merge = trsct_merge.merge(trans_type2_st, on=tr_hd.trans_type2, how='left')\n",
    "# trsct_merge = trsct_merge.merge(market_code_st, on=tr_hd.market_code, how='left')\n",
    "# trsct_merge = trsct_merge.merge(market_type_st, on=tr_hd.market_type, how='left')\n",
    "# trsct_merge = trsct_merge.merge(ip1_sub_st, on=tr_hd.ip1_sub, how='left')\n",
    "trsct_merge.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['time',\n",
       " 'amt_src1',\n",
       " 'merchant',\n",
       " 'code1',\n",
       " 'code2',\n",
       " 'trans_type1',\n",
       " 'acc_id1',\n",
       " 'device_code1',\n",
       " 'device_code2',\n",
       " 'device_code3',\n",
       " 'device1',\n",
       " 'device2',\n",
       " 'mac1',\n",
       " 'ip1',\n",
       " 'amt_src2',\n",
       " 'acc_id2',\n",
       " 'acc_id3',\n",
       " 'geo_code',\n",
       " 'market_code',\n",
       " 'ip1_sub']"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "object_col = trsct_merge.select_dtypes(['object']).columns.values.tolist()\n",
    "object_col"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['acc_id1',\n",
       " 'time',\n",
       " 'trans_type2_trsct_cnt_rate',\n",
       " 'trans_type1',\n",
       " 'trans_type2_uid_cnt',\n",
       " 'ip1',\n",
       " 'time_uid_cnt',\n",
       " 'device1',\n",
       " 'ip1_sub',\n",
       " 'trans_type1_uid_nunique',\n",
       " 'market_type_uid_nunique',\n",
       " 'market_type',\n",
       " 'device_code2',\n",
       " 'merchant',\n",
       " 'market_type_uid_cnt',\n",
       " 'acc_id3',\n",
       " 'day_trsct_nunique_rate',\n",
       " 'day_uid_nunique',\n",
       " 'channel',\n",
       " 'device_code3',\n",
       " 'geo_code',\n",
       " 'mac1',\n",
       " 'code2',\n",
       " 'time_uid_nunique',\n",
       " 'market_type_trsct_nunique_rate',\n",
       " 'trans_type2_uid_nunique',\n",
       " 'trans_type1_uid_cnt',\n",
       " 'amt_src1',\n",
       " 'channel_trsct_nunique_rate',\n",
       " 'device_code1',\n",
       " 'market_type_trsct_cnt_rate',\n",
       " 'trans_type1_trsct_nunique_rate',\n",
       " 'code1',\n",
       " 'trans_type1_trsct_cnt_rate',\n",
       " 'amt_src1_uid_nunique',\n",
       " 'amt_src1_trsct_cnt_rate',\n",
       " 'channel_uid_cnt',\n",
       " 'channel_uid_nunique',\n",
       " 'day_trsct_cnt_rate',\n",
       " 'trans_type2_trsct_nunique_rate',\n",
       " 'Tag',\n",
       " 'channel_trsct_cnt_rate',\n",
       " 'acc_id2',\n",
       " 'market_code',\n",
       " 'device2',\n",
       " 'day',\n",
       " 'amt_src2',\n",
       " 'time_trsct_cnt_rate',\n",
       " 'trans_type2',\n",
       " 'time_trsct_nunique_rate',\n",
       " 'mac1_uid_nunique',\n",
       " 'day_uid_cnt',\n",
       " 'amt_src1_uid_cnt',\n",
       " 'amt_src1_trsct_nunique_rate']"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trsct_merge_nunique = trsct_merge.nunique()\n",
    "trsct_merge_nunique.sort_values(inplace=True)\n",
    "one_hot_col = trsct_merge_nunique[trsct_merge_nunique < 50].index.values.tolist() \n",
    "one_hot_col = list(set(one_hot_col) | set(object_col))\n",
    "one_hot_col"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(435369, 104)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>UID</th>\n",
       "      <th>channel</th>\n",
       "      <th>day</th>\n",
       "      <th>trans_amt</th>\n",
       "      <th>bal</th>\n",
       "      <th>trans_type2</th>\n",
       "      <th>market_type</th>\n",
       "      <th>Tag</th>\n",
       "      <th>day_uid_cnt</th>\n",
       "      <th>day_trsct_cnt_rate</th>\n",
       "      <th>...</th>\n",
       "      <th>market_code_uid_nunique</th>\n",
       "      <th>market_code_trsct_nunique_rate</th>\n",
       "      <th>market_type_uid_cnt</th>\n",
       "      <th>market_type_trsct_cnt_rate</th>\n",
       "      <th>market_type_uid_nunique</th>\n",
       "      <th>market_type_trsct_nunique_rate</th>\n",
       "      <th>ip1_sub_uid_cnt</th>\n",
       "      <th>ip1_sub_trsct_cnt_rate</th>\n",
       "      <th>ip1_sub_uid_nunique</th>\n",
       "      <th>ip1_sub_trsct_nunique_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10000</td>\n",
       "      <td>140.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>5536.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>105.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>10894</td>\n",
       "      <td>0.025022</td>\n",
       "      <td>...</td>\n",
       "      <td>17</td>\n",
       "      <td>0.000751</td>\n",
       "      <td>182362</td>\n",
       "      <td>0.418868</td>\n",
       "      <td>20677</td>\n",
       "      <td>2.754172</td>\n",
       "      <td>3</td>\n",
       "      <td>0.000007</td>\n",
       "      <td>2</td>\n",
       "      <td>0.000019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10000</td>\n",
       "      <td>140.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>5536.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>105.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>10894</td>\n",
       "      <td>0.025022</td>\n",
       "      <td>...</td>\n",
       "      <td>17</td>\n",
       "      <td>0.000751</td>\n",
       "      <td>182362</td>\n",
       "      <td>0.418868</td>\n",
       "      <td>20677</td>\n",
       "      <td>2.754172</td>\n",
       "      <td>3</td>\n",
       "      <td>0.000007</td>\n",
       "      <td>2</td>\n",
       "      <td>0.000019</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 104 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     UID  channel   day  trans_amt    bal  trans_type2  market_type  Tag  \\\n",
       "0  10000    140.0  26.0     5536.0  100.0        105.0          1.0    1   \n",
       "1  10000    140.0  26.0     5536.0  100.0        105.0          1.0    1   \n",
       "\n",
       "   day_uid_cnt  day_trsct_cnt_rate             ...              \\\n",
       "0        10894            0.025022             ...               \n",
       "1        10894            0.025022             ...               \n",
       "\n",
       "   market_code_uid_nunique  market_code_trsct_nunique_rate  \\\n",
       "0                       17                        0.000751   \n",
       "1                       17                        0.000751   \n",
       "\n",
       "   market_type_uid_cnt  market_type_trsct_cnt_rate  market_type_uid_nunique  \\\n",
       "0               182362                    0.418868                    20677   \n",
       "1               182362                    0.418868                    20677   \n",
       "\n",
       "   market_type_trsct_nunique_rate  ip1_sub_uid_cnt  ip1_sub_trsct_cnt_rate  \\\n",
       "0                        2.754172                3                0.000007   \n",
       "1                        2.754172                3                0.000007   \n",
       "\n",
       "   ip1_sub_uid_nunique  ip1_sub_trsct_nunique_rate  \n",
       "0                    2                    0.000019  \n",
       "1                    2                    0.000019  \n",
       "\n",
       "[2 rows x 104 columns]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# one_hot_col.remove('Tag')\n",
    "trsct_merge = trsct_merge.drop(object_col, axis=1)\n",
    "print(trsct_merge.shape)\n",
    "trsct_merge.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "123\n"
     ]
    }
   ],
   "source": [
    "trsct_merge.to_csv(data_base_path+ 'trsct_merge.csv', index=False)\n",
    "print(123)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "channel\n",
      "day\n",
      "trans_amt\n",
      "bal\n",
      "trans_type2\n",
      "market_type\n",
      "day_uid_cnt\n",
      "day_trsct_cnt_rate\n",
      "day_uid_nunique\n",
      "day_trsct_nunique_rate\n",
      "channel_uid_cnt\n",
      "channel_trsct_cnt_rate\n",
      "channel_uid_nunique\n",
      "channel_trsct_nunique_rate\n",
      "trans_amt_uid_cnt\n",
      "trans_amt_trsct_cnt_rate\n",
      "trans_amt_uid_nunique\n",
      "trans_amt_trsct_nunique_rate\n",
      "time_uid_cnt\n",
      "time_trsct_cnt_rate\n",
      "time_uid_nunique\n",
      "time_trsct_nunique_rate\n",
      "amt_src1_uid_cnt\n",
      "amt_src1_trsct_cnt_rate\n",
      "amt_src1_uid_nunique\n",
      "amt_src1_trsct_nunique_rate\n",
      "merchant_uid_cnt\n",
      "merchant_trsct_cnt_rate\n",
      "merchant_uid_nunique\n",
      "merchant_trsct_nunique_rate\n",
      "code1_uid_cnt\n",
      "code1_trsct_cnt_rate\n",
      "code1_uid_nunique\n",
      "code1_trsct_nunique_rate\n",
      "code2_uid_cnt\n",
      "code2_trsct_cnt_rate\n",
      "code2_uid_nunique\n",
      "code2_trsct_nunique_rate\n",
      "trans_type1_uid_cnt\n",
      "trans_type1_trsct_cnt_rate\n",
      "trans_type1_uid_nunique\n",
      "trans_type1_trsct_nunique_rate\n",
      "acc_id1_uid_cnt\n",
      "acc_id1_trsct_cnt_rate\n",
      "acc_id1_uid_nunique\n",
      "acc_id1_trsct_nunique_rate\n",
      "mac1_uid_cnt\n",
      "mac1_trsct_cnt_rate\n",
      "mac1_uid_nunique\n",
      "mac1_trsct_nunique_rate\n",
      "device2_uid_cnt\n",
      "device2_trsct_cnt_rate\n",
      "device2_uid_nunique\n",
      "device2_trsct_nunique_rate\n",
      "device1_uid_cnt\n",
      "device1_trsct_cnt_rate\n",
      "device1_uid_nunique\n",
      "device1_trsct_nunique_rate\n",
      "device_code3_uid_cnt\n",
      "device_code3_trsct_cnt_rate\n",
      "device_code3_uid_nunique\n",
      "device_code3_trsct_nunique_rate\n",
      "bal_uid_cnt\n",
      "bal_trsct_cnt_rate\n",
      "bal_uid_nunique\n",
      "bal_trsct_nunique_rate\n",
      "ip1_uid_cnt\n",
      "ip1_trsct_cnt_rate\n",
      "ip1_uid_nunique\n",
      "ip1_trsct_nunique_rate\n",
      "amt_src2_uid_cnt\n",
      "amt_src2_trsct_cnt_rate\n",
      "amt_src2_uid_nunique\n",
      "amt_src2_trsct_nunique_rate\n",
      "acc_id2_uid_cnt\n",
      "acc_id2_trsct_cnt_rate\n",
      "acc_id2_uid_nunique\n",
      "acc_id2_trsct_nunique_rate\n",
      "acc_id3_uid_cnt\n",
      "acc_id3_trsct_cnt_rate\n",
      "acc_id3_uid_nunique\n",
      "acc_id3_trsct_nunique_rate\n",
      "geocode_uid_cnt\n",
      "geocode_trsct_cnt_rate\n",
      "geocode_uid_nunique\n",
      "geocode_trsct_nunique_rate\n",
      "trans_type2_uid_cnt\n",
      "trans_type2_trsct_cnt_rate\n",
      "trans_type2_uid_nunique\n",
      "trans_type2_trsct_nunique_rate\n",
      "market_code_uid_cnt\n",
      "market_code_trsct_cnt_rate\n",
      "market_code_uid_nunique\n",
      "market_code_trsct_nunique_rate\n",
      "market_type_uid_cnt\n",
      "market_type_trsct_cnt_rate\n",
      "market_type_uid_nunique\n",
      "market_type_trsct_nunique_rate\n",
      "ip1_sub_uid_cnt\n",
      "ip1_sub_trsct_cnt_rate\n",
      "ip1_sub_uid_nunique\n",
      "ip1_sub_trsct_nunique_rate\n",
      "(435369, 818)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>UID</th>\n",
       "      <th>Tag</th>\n",
       "      <th>channel_x</th>\n",
       "      <th>channel_y</th>\n",
       "      <th>channel_x</th>\n",
       "      <th>channel_y</th>\n",
       "      <th>channel_x</th>\n",
       "      <th>channel_y</th>\n",
       "      <th>channel_x</th>\n",
       "      <th>channel_y</th>\n",
       "      <th>...</th>\n",
       "      <th>ip1_sub_uid_nunique_x</th>\n",
       "      <th>ip1_sub_uid_nunique_y</th>\n",
       "      <th>ip1_sub_trsct_nunique_rate_x</th>\n",
       "      <th>ip1_sub_trsct_nunique_rate_y</th>\n",
       "      <th>ip1_sub_trsct_nunique_rate_x</th>\n",
       "      <th>ip1_sub_trsct_nunique_rate_y</th>\n",
       "      <th>ip1_sub_trsct_nunique_rate_x</th>\n",
       "      <th>ip1_sub_trsct_nunique_rate_y</th>\n",
       "      <th>ip1_sub_trsct_nunique_rate_x</th>\n",
       "      <th>ip1_sub_trsct_nunique_rate_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10000</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>140.0</td>\n",
       "      <td>140.0</td>\n",
       "      <td>280.0</td>\n",
       "      <td>140.000000</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000019</td>\n",
       "      <td>0.000019</td>\n",
       "      <td>0.000038</td>\n",
       "      <td>0.000019</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10000</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>140.0</td>\n",
       "      <td>140.0</td>\n",
       "      <td>280.0</td>\n",
       "      <td>140.000000</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000019</td>\n",
       "      <td>0.000019</td>\n",
       "      <td>0.000038</td>\n",
       "      <td>0.000019</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10001</td>\n",
       "      <td>0</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>140.0</td>\n",
       "      <td>102.0</td>\n",
       "      <td>1988.0</td>\n",
       "      <td>110.444444</td>\n",
       "      <td>16.25612</td>\n",
       "      <td>264.261438</td>\n",
       "      <td>...</td>\n",
       "      <td>7.061124</td>\n",
       "      <td>49.859477</td>\n",
       "      <td>18</td>\n",
       "      <td>9</td>\n",
       "      <td>0.000658</td>\n",
       "      <td>0.000083</td>\n",
       "      <td>0.005873</td>\n",
       "      <td>0.000326</td>\n",
       "      <td>0.000154</td>\n",
       "      <td>2.364154e-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10001</td>\n",
       "      <td>0</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>140.0</td>\n",
       "      <td>102.0</td>\n",
       "      <td>1988.0</td>\n",
       "      <td>110.444444</td>\n",
       "      <td>16.25612</td>\n",
       "      <td>264.261438</td>\n",
       "      <td>...</td>\n",
       "      <td>7.061124</td>\n",
       "      <td>49.859477</td>\n",
       "      <td>18</td>\n",
       "      <td>9</td>\n",
       "      <td>0.000658</td>\n",
       "      <td>0.000083</td>\n",
       "      <td>0.005873</td>\n",
       "      <td>0.000326</td>\n",
       "      <td>0.000154</td>\n",
       "      <td>2.364154e-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10001</td>\n",
       "      <td>0</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>140.0</td>\n",
       "      <td>102.0</td>\n",
       "      <td>1988.0</td>\n",
       "      <td>110.444444</td>\n",
       "      <td>16.25612</td>\n",
       "      <td>264.261438</td>\n",
       "      <td>...</td>\n",
       "      <td>7.061124</td>\n",
       "      <td>49.859477</td>\n",
       "      <td>18</td>\n",
       "      <td>9</td>\n",
       "      <td>0.000658</td>\n",
       "      <td>0.000083</td>\n",
       "      <td>0.005873</td>\n",
       "      <td>0.000326</td>\n",
       "      <td>0.000154</td>\n",
       "      <td>2.364154e-08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 818 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     UID  Tag  channel_x  channel_y  channel_x  channel_y  channel_x  \\\n",
       "0  10000    1          2          1      140.0      140.0      280.0   \n",
       "1  10000    1          2          1      140.0      140.0      280.0   \n",
       "2  10001    0         18          2      140.0      102.0     1988.0   \n",
       "3  10001    0         18          2      140.0      102.0     1988.0   \n",
       "4  10001    0         18          2      140.0      102.0     1988.0   \n",
       "\n",
       "    channel_y  channel_x   channel_y              ...               \\\n",
       "0  140.000000    0.00000    0.000000              ...                \n",
       "1  140.000000    0.00000    0.000000              ...                \n",
       "2  110.444444   16.25612  264.261438              ...                \n",
       "3  110.444444   16.25612  264.261438              ...                \n",
       "4  110.444444   16.25612  264.261438              ...                \n",
       "\n",
       "   ip1_sub_uid_nunique_x  ip1_sub_uid_nunique_y  ip1_sub_trsct_nunique_rate_x  \\\n",
       "0               0.000000               0.000000                             2   \n",
       "1               0.000000               0.000000                             2   \n",
       "2               7.061124              49.859477                            18   \n",
       "3               7.061124              49.859477                            18   \n",
       "4               7.061124              49.859477                            18   \n",
       "\n",
       "   ip1_sub_trsct_nunique_rate_y  ip1_sub_trsct_nunique_rate_x  \\\n",
       "0                             1                      0.000019   \n",
       "1                             1                      0.000019   \n",
       "2                             9                      0.000658   \n",
       "3                             9                      0.000658   \n",
       "4                             9                      0.000658   \n",
       "\n",
       "   ip1_sub_trsct_nunique_rate_y  ip1_sub_trsct_nunique_rate_x  \\\n",
       "0                      0.000019                      0.000038   \n",
       "1                      0.000019                      0.000038   \n",
       "2                      0.000083                      0.005873   \n",
       "3                      0.000083                      0.005873   \n",
       "4                      0.000083                      0.005873   \n",
       "\n",
       "   ip1_sub_trsct_nunique_rate_y  ip1_sub_trsct_nunique_rate_x  \\\n",
       "0                      0.000019                      0.000000   \n",
       "1                      0.000019                      0.000000   \n",
       "2                      0.000326                      0.000154   \n",
       "3                      0.000326                      0.000154   \n",
       "4                      0.000326                      0.000154   \n",
       "\n",
       "   ip1_sub_trsct_nunique_rate_y  \n",
       "0                  0.000000e+00  \n",
       "1                  0.000000e+00  \n",
       "2                  2.364154e-08  \n",
       "3                  2.364154e-08  \n",
       "4                  2.364154e-08  \n",
       "\n",
       "[5 rows x 818 columns]"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def get_features(data):\n",
    "    label = data[tag_header].drop_duplicates()\n",
    "    uid_gb = data.groupby(['UID'])\n",
    "    cols = data.columns.values.tolist()\n",
    "    cols.remove(tag_hd.Tag)\n",
    "    cols.remove(tag_hd.UID)\n",
    "    for feature in cols:\n",
    "        print(feature)\n",
    "        label = label.merge(uid_gb[feature].count().reset_index(), on='UID', how='left')\n",
    "        label = label.merge(uid_gb[feature].nunique().reset_index(), on='UID', how='left')\n",
    "        label = label.merge(uid_gb[feature].max().reset_index(), on='UID', how='left')\n",
    "        label = label.merge(uid_gb[feature].min().reset_index(), on='UID', how='left')\n",
    "        label = label.merge(uid_gb[feature].sum().reset_index(), on='UID', how='left')\n",
    "        label = label.merge(uid_gb[feature].mean().reset_index(), on='UID', how='left')\n",
    "        label = label.merge(uid_gb[feature].std().reset_index(), on='UID', how='left')\n",
    "        label = label.merge(uid_gb[feature].var().reset_index(), on='UID', how='left')\n",
    "    return label\n",
    "\n",
    "trsct_merge_ftrs = get_features(trsct_merge)\n",
    "trsct_merge_ftrs.to_csv(features_base_path+ 'trsct_merge_ftrs.csv', index=False)\n",
    "print(trsct_merge_ftrs.shape)\n",
    "trsct_merge_ftrs.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "ename": "ValueError",
     "evalue": "Buffer has wrong number of dimensions (expected 1, got 2)",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mValueError\u001b[0m                                Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-65-8a58ce9b5d62>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m()\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[0mcols_op\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mtrsct_merge_ftrs\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mvalues\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mtolist\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m \u001b[0mlabel\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mtrsct_merge_ftrs\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mcols_op\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdrop_duplicates\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      3\u001b[0m \u001b[0mprint\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mlabel\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mshape\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[1;31m# trsct_merge_ftrs01= label.merge(trsct_merge_ftrs, on='UID', how='left')\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m \u001b[0mlabel\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mto_csv\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mfeatures_base_path\u001b[0m\u001b[1;33m+\u001b[0m \u001b[1;34m'trsct_merge_ftrs.csv'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mindex\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mFalse\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pandas\\core\\frame.py\u001b[0m in \u001b[0;36mdrop_duplicates\u001b[1;34m(self, subset, keep, inplace)\u001b[0m\n\u001b[0;32m   3096\u001b[0m         \"\"\"\n\u001b[0;32m   3097\u001b[0m         \u001b[0minplace\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mvalidate_bool_kwarg\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0minplace\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m'inplace'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 3098\u001b[1;33m         \u001b[0mduplicated\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mduplicated\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0msubset\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mkeep\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mkeep\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   3099\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   3100\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0minplace\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pandas\\core\\frame.py\u001b[0m in \u001b[0;36mduplicated\u001b[1;34m(self, subset, keep)\u001b[0m\n\u001b[0;32m   3142\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   3143\u001b[0m         \u001b[0mvals\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mcol\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mvalues\u001b[0m \u001b[1;32mfor\u001b[0m \u001b[0mcol\u001b[0m \u001b[1;32min\u001b[0m \u001b[0msubset\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 3144\u001b[1;33m         \u001b[0mlabels\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mshape\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mmap\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mlist\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mzip\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m*\u001b[0m\u001b[0mmap\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mf\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mvals\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   3145\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   3146\u001b[0m         \u001b[0mids\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mget_group_index\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mlabels\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mshape\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0msort\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mFalse\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mxnull\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mFalse\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pandas\\core\\frame.py\u001b[0m in \u001b[0;36mf\u001b[1;34m(vals)\u001b[0m\n\u001b[0;32m   3131\u001b[0m         \u001b[1;32mdef\u001b[0m \u001b[0mf\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mvals\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   3132\u001b[0m             labels, shape = algorithms.factorize(\n\u001b[1;32m-> 3133\u001b[1;33m                 vals, size_hint=min(len(self), _SIZE_HINT_LIMIT))\n\u001b[0m\u001b[0;32m   3134\u001b[0m             \u001b[1;32mreturn\u001b[0m \u001b[0mlabels\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mastype\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'i8'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcopy\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mFalse\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mlen\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mshape\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   3135\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pandas\\core\\algorithms.py\u001b[0m in \u001b[0;36mfactorize\u001b[1;34m(values, sort, order, na_sentinel, size_hint)\u001b[0m\n\u001b[0;32m    558\u001b[0m     \u001b[0muniques\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mvec_klass\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    559\u001b[0m     \u001b[0mcheck_nulls\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0mis_integer_dtype\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0moriginal\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 560\u001b[1;33m     \u001b[0mlabels\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mtable\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget_labels\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mvalues\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0muniques\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;36m0\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mna_sentinel\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcheck_nulls\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    561\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    562\u001b[0m     \u001b[0mlabels\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0m_ensure_platform_int\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mlabels\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mpandas\\_libs\\hashtable_class_helper.pxi\u001b[0m in \u001b[0;36mpandas._libs.hashtable.Float64HashTable.get_labels (pandas\\_libs\\hashtable.c:8705)\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;31mValueError\u001b[0m: Buffer has wrong number of dimensions (expected 1, got 2)"
     ]
    }
   ],
   "source": [
    "cols_op = trsct_merge_ftrs.columns.values.tolist()\n",
    "label = trsct_merge_ftrs[cols_op].drop_duplicates()\n",
    "print(label.shape)\n",
    "# trsct_merge_ftrs01= label.merge(trsct_merge_ftrs, on='UID', how='left')\n",
    "label.to_csv(features_base_path+ 'trsct_merge_ftrs.csv', index=False)\n",
    "# label.to_csv(features_base_path+ 'trsct_merge_ftrs.csv', index=False)\n",
    "# print(trsct_merge_ftrs01.shape)\n",
    "# trsct_merge_ftrs01.head(5)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda root]",
   "language": "python",
   "name": "conda-root-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}
